﻿Imports System.Data.OleDb
Imports Entities

Public Class ConectadoAccess
    Private conexion As OleDbConnection
    Private Const DB As String = "Biblioteca.accdb"
    Private Const cadConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DB

#Region "Métodos básicos: Conectar(), Desconectar()"
    'Si no estamos conectados a la base de datos, crea una conexion con la BD
    Private Sub Conectar()
        Try
            If IsNothing(conexion) Then
                conexion = New OleDbConnection(cadConexion)
            End If
            If conexion.State = ConnectionState.Closed Then
                conexion.Open()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    'Si estamos conectados a la base de datos, nos desconecta
    Private Sub Desconectar()
        If Not IsNothing(conexion) Then
            conexion.Close()
        End If
    End Sub

#End Region

#Region "Metodos de BD: GetLibros(), GetAutores(), GetCategorias(), GetClientes(), getPrestamos(), getSoportes()"
    ''' <summary>
    ''' Obtiene todos los libros de la base de datos
    ''' </summary>
    ''' <returns>ObjectModel.ReadOnlyCollection(Of Libro)</returns>
    ''' <remarks></remarks>
    Public Function GetLibros() As ObjectModel.ReadOnlyCollection(Of Libro)
        conexion.Open()
        Dim listaLibros As New List(Of Libro)

        Dim command As New OleDbCommand("SELECT * FROM Libro", conexion)
        Dim dataReader As OleDbDataReader = command.ExecuteReader

        While dataReader.Read
            listaLibros.Add(New Libro(dataReader("Isbn"), dataReader("CodCategoria"), dataReader("CodSoporte"), dataReader("Titulo"), dataReader("Ejemplares"), dataReader("CodAutor")))
        End While
        conexion.Close()
        Return listaLibros.AsReadOnly
    End Function

    ''' <summary>
    ''' Obtiene todos los autores de la base de datos
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAutores() As ObjectModel.ReadOnlyCollection(Of Autor)
        conexion.Open()
        Dim listaAutores As New List(Of Autor)

        Dim command As New OleDbCommand("SELECT * FROM Autor", conexion)
        Dim dataReader As OleDbDataReader = command.ExecuteReader

        While dataReader.Read
            listaAutores.Add(New Autor(dataReader("CodAutor"), dataReader("Nombre"), dataReader("Apellidos")))
        End While
        conexion.Close()
        Return listaAutores.AsReadOnly
    End Function

    ''' <summary>
    ''' Obtiene todas las categorías de la base de datos
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetCategorias() As ObjectModel.ReadOnlyCollection(Of Categoria)
        conexion.Open()
        Dim listaCategorias As New List(Of Categoria)

        Dim command As New OleDbCommand("SELECT * FROM Categoria", conexion)
        Dim dataReader As OleDbDataReader = command.ExecuteReader

        While dataReader.Read
            listaCategorias.Add(New Categoria(dataReader("CodCategoria"), dataReader("Nombre")))
        End While
        conexion.Close()
        Return listaCategorias.AsReadOnly
    End Function

    ''' <summary>
    ''' Obtiene todos los clientes de la base de datos
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetClientes() As ObjectModel.ReadOnlyCollection(Of Cliente)
        conexion.Open()
        Dim listaClientes As New List(Of Cliente)

        Dim command As New OleDbCommand("SELECT * FROM Cliente", conexion)
        Dim dataReader As OleDbDataReader = command.ExecuteReader

        While dataReader.Read
            listaClientes.Add(New Cliente(dataReader("Dni"), dataReader("Nombre"), dataReader("Apellidos"), dataReader("Telefono")))
        End While
        conexion.Close()
        Return listaClientes.AsReadOnly
    End Function

    ''' <summary>
    ''' Obtiene todos los prestamos de la base de datos
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getPrestamos() As ObjectModel.ReadOnlyCollection(Of Prestamo)
        conexion.Open()
        Dim listaPrestamo As New List(Of Prestamo)

        Dim command As New OleDbCommand("SELECT * FROM Prestamo", conexion)
        Dim dataReader As OleDbDataReader = command.ExecuteReader

        While dataReader.Read
            listaPrestamo.Add(New Prestamo(dataReader("Isbn"), dataReader("Dni"), dataReader("FechaPrestamo"), dataReader("FechaDevolucion")))
        End While
        conexion.Close()
        Return listaPrestamo.AsReadOnly
    End Function

    ''' <summary>
    ''' Obtiene todos los soportes de la base de datos
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSoportes() As ObjectModel.ReadOnlyCollection(Of Soporte)
        conexion.Open()
        Dim listaSoporte As New List(Of Soporte)

        Dim command As New OleDbCommand("SELECT * FROM Prestamo", conexion)
        Dim dataReader As OleDbDataReader = command.ExecuteReader

        While dataReader.Read
            listaSoporte.Add(New Soporte(dataReader("CodSoporte"), dataReader("Nombre")))
        End While
        conexion.Close()
        Return listaSoporte.AsReadOnly
    End Function
#End Region

#Region "Consultas: GetLibrosByTitle(String), GetLibrosByCategory(Integer)"
    Public Function GetLibrosByTitle(ByVal tituloLibro As Libro) As ObjectModel.ReadOnlyCollection(Of Libro)
        conexion.Open()
        Dim listaLibros As New List(Of Libro)
        Dim listaAutores As New List(Of String)

        Dim command As New OleDbCommand("SELECT * FROM Libro INNER JOIN Autor ON Libro.Isbn = Autor.Isbn WHERE Libro.Titulo ='" & tituloLibro.Titulo & "'", conexion)
        Dim dataReader As OleDbDataReader = command.ExecuteReader

        While dataReader.Read
            listaAutores.Add(dataReader("CodAutor"))
            listaLibros.Add(New Libro(dataReader("Isbn"), dataReader("CodCategoria"), dataReader("CodSoporte"), dataReader("Titulo"), dataReader("Ejemplares"), listaAutores))
        End While
        conexion.Close()
        Return listaLibros.AsReadOnly
    End Function

    Public Function GetLibrosByCategory(ByVal categoria As Categoria) As ObjectModel.ReadOnlyCollection(Of Libro)
        conexion.Open()
        Dim listaLibros As New List(Of Libro)

        Dim command As New OleDbCommand("SELECT * FROM Libro INNER JOIN Categoria ON Libro.CodCategoria = Categoria.CodCategoria Where Categoria.Nombre = '" & categoria.Nombre & "'", conexion)
        Dim dataReader As OleDbDataReader = command.ExecuteReader

        While dataReader.Read
            listaLibros.Add(New Libro(dataReader("Isbn"), dataReader("CodCategoria"), Integer.Parse(dataReader("CodSoporte")), dataReader("Titulo"), dataReader("Ejemplares")))
        End While
        conexion.Close()
        Return listaLibros.AsReadOnly
    End Function

    Public Function getMorosos() As ObjectModel.ReadOnlyCollection(Of Cliente)
        conexion.Open()
        Dim listaMorosos As New List(Of Cliente)
        Dim commandoGetMorosos As New OleDbCommand("SELECT * FROM Cliente INNER JOIN Prestamo ON Cliente.Dni = Prestamo.Dni", conexion)
        Dim dataReaderMorosos As OleDbDataReader = commandoGetMorosos.ExecuteReader()

        While dataReaderMorosos.Read
            For i As Integer = 0 To getPrestamos().Count - 1
                If (getPrestamos(i).Dni.Contains(dataReaderMorosos("Dni"))) Then
                    Dim fecha As String() = getPrestamos(i).FechaDevolucion.Split("/")

                    If (Integer.Parse(fecha(2)) < (Today.Year)) Then
                        listaMorosos.Add(New Cliente(dataReaderMorosos("Isbn"), dataReaderMorosos("Nombre"), dataReaderMorosos("Apelidos"), dataReaderMorosos("Telefono")))
                    Else
                        If (Integer.Parse(fecha(1) - 1) < (Today.Month)) Then
                            listaMorosos.Add(New Cliente(dataReaderMorosos("Isbn"), dataReaderMorosos("Nombre"), dataReaderMorosos("Apelidos"), dataReaderMorosos("Telefono")))
                        Else
                            If (Integer.Parse(fecha(0)) < (Today.Day)) Then
                                listaMorosos.Add(New Cliente(dataReaderMorosos("Isbn"), dataReaderMorosos("Nombre"), dataReaderMorosos("Apelidos"), dataReaderMorosos("Telefono")))
                            End If
                        End If
                    End If
                End If
            Next
        End While
        conexion.Close()
        Return listaMorosos.AsReadOnly
    End Function

    Public Function getPrestamosByCliente(ByVal client As Cliente) As ObjectModel.ReadOnlyCollection(Of Prestamo)
        conexion.Open()
        Dim listaPrestamos As New List(Of Prestamo)

        Dim command As New OleDbCommand("SELECT * FROM Prestamos INNER JOIN Cliente ON Prestamo.Dni = Cliente.Dni WHERE Cliente.Dni='" & client.Dni & "'", conexion)
        Dim dataReader As OleDbDataReader = command.ExecuteReader

        While dataReader.Read
            listaPrestamos.Add(New Prestamo(dataReader("Isbn"), dataReader("Dni"), dataReader("FechaPrestamo"), dataReader("FechaDevolucion")))
        End While
        conexion.Close()
        Return listaPrestamos.AsReadOnly
    End Function

    Public Function getClientesPorNombreLibroPrestado(libro As Libro) As ObjectModel.ReadOnlyCollection(Of Cliente)
        conexion.Open()
        Dim listaclientes As New List(Of Cliente)

        Dim sqlSelectClientesPorLibroPrestado As String = "SELECT * FROM (Cliente INNER JOIN Prestamo ON Cliente.Dni = Prestamo.Dni) INNER JOIN Libro ON Prestamo.Isbn = Libro.Isbn WHERE Libro.Titulo='" & libro.Titulo & "'"

        Dim commandoSelectClientes As New OleDbCommand(sqlSelectClientesPorLibroPrestado, conexion)

        Dim dataReaderClientesConEjemplar As OleDbDataReader = commandoSelectClientes.ExecuteReader()

        While dataReaderClientesConEjemplar.Read
            listaclientes.Add(New Cliente(dataReaderClientesConEjemplar("Dni"), dataReaderClientesConEjemplar("Nombre"), dataReaderClientesConEjemplar("Apellidos"), dataReaderClientesConEjemplar("Telefono")))
        End While

        conexion.Close()
        Return listaclientes.AsReadOnly
    End Function

#End Region

#Region "Inserts y Updates"
    Public Sub DevolverLibro(miPrestamo As Prestamo)
        Conectar()
        Dim listaPrestamos As List(Of Prestamo) = getPrestamos.ToList

        Dim sqlUpdateEjemplares As String = "UPDATE Prestamo SET Entregado=True WHERE Isbn='" & miPrestamo.Isbn & "'"

        Dim comandoUpdateEjemplares As New OleDbCommand(sqlUpdateEjemplares, conexion)

        comandoUpdateEjemplares.ExecuteNonQuery()
        Desconectar()
    End Sub

    Public Sub PrestarLibro(miPrestamo As Prestamo)
        Conectar()
        Dim listaPrestados As List(Of Prestamo) = getPrestamos().ToList

        Dim sqlInsertPrestamo As String = "INSERT INTO Prestamo VALUES('" & miPrestamo.Isbn & "','" & miPrestamo.Dni & "','" & miPrestamo.FechaPrestamo & "','" & miPrestamo.FechaDevolucion & "'," & miPrestamo.Entregado & ")"

        Dim sqlUpdateEjemplares As String = "UPDATE Libro SET Ejemplares=Ejemplares-1 WHERE Isbn='" & miPrestamo.Isbn & "'"

        Dim comandoUpdateEjemplares As New OleDbCommand(sqlUpdateEjemplares, conexion)

        Dim comandoInsertPrestamo As New OleDbCommand(sqlInsertPrestamo, conexion)

        comandoUpdateEjemplares.ExecuteNonQuery()
        comandoInsertPrestamo.ExecuteNonQuery()
        Desconectar()
    End Sub

#End Region

End Class
